﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using EasyDentalMail.View;

namespace EasyDentalMail.DAO
{
    public class GoogleDAL
    {
        private string connString = System.Configuration.ConfigurationManager.ConnectionStrings["edsConn"].ToString();
        public GoogleView getGoogle(AgendaView ag)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = montarSelectGoogle(ag);
                    cmd.Connection = conn;
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    GoogleView goV = null;
                    if (dr.Read())
                    {
                        goV = montarDRGoogle(dr);
                    }
                    conn.Close();
                    return goV;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Erro ao cadastrar GOOGLECALENDAR TABLE", ex);
            }
        }

        public List<GoogleView> listar()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = montarSelectLista();
                    cmd.Connection = conn;
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    List<GoogleView> goV = new List<GoogleView>();
                    while (dr.Read())
                    {
                        goV.Add(montarDRGoogle(dr));
                    }
                    conn.Close();
                    return goV;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void deletar(int Id)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = "DELETE FROM GOOGLECALENDAR WHERE ID = "+Id;
                    cmd.Connection = conn;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<GoogleView> listarDelete()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = montarListaDelete();
                    cmd.Connection = conn;
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    List<GoogleView> goV = new List<GoogleView>();
                    while (dr.Read())
                    {
                        goV.Add(montarDRGoogle(dr));
                    }
                    conn.Close();
                    return goV;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void insert(GoogleView gv)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = montarInsert(gv);
                    cmd.Connection = conn;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string montarListaDelete()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID, ID_PRESTADOR, ID_UNIDADE, DATA, HORAINICIO, CALENDARID ");
            sb.Append("FROM GOOGLECALENDAR ");
            sb.Append("WHERE (CAST(ID_PRESTADOR AS VARCHAR)+'_'+ CAST(ID_UNIDADE AS VARCHAR)+'_'+CAST(HORAINICIO AS VARCHAR)+'_'+CAST(DATA AS VARCHAR)) NOT IN ( ");
            sb.Append("SELECT CAST(ID_PRESTADOR AS VARCHAR)+'_'+ CAST(ID_UNIDADE AS VARCHAR)+'_'+CAST(HORAINICIO AS VARCHAR)+'_'+CAST(DATA AS VARCHAR) ");
            sb.Append("FROM AGENDA) ");
            return sb.ToString();
        }

        private string montarInsert(GoogleView gV)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO GOOGLECALENDAR ");
            sb.Append("(ID_PRESTADOR, ID_UNIDADE, DATA, HORAINICIO, CALENDARID) ");
            sb.Append("VALUES ");
            sb.Append("({0},{1},'{2}',{3},'{4}') ");
            return string.Format(sb.ToString(),gV.IdPrestador,gV.IdUnidade,gV.Data.ToString("yyyy-MM-dd 00:00:00"),gV.HoraInicio,gV.CalendarId);
        }

        private string montarSelectLista()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select  ID, ID_PRESTADOR, ID_UNIDADE, DATA, HORAINICIO, CALENDARID ");
            sb.Append("from GOOGLECALENDAR ");
            sb.Append("where DATA >= '{0}' ");
            return string.Format(sb.ToString(), DateTime.Today.ToString("yyyy-MM-dd 00:00:00"));
        }

        private GoogleView montarDRGoogle(SqlDataReader dr)
        {
            GoogleView ag = new GoogleView();
            if (!dr.IsDBNull(0))
                ag.Id = dr.GetInt32(0);
            if (!dr.IsDBNull(1))
                ag.IdPrestador = dr.GetInt32(1);
            if (!dr.IsDBNull(2))
                ag.IdUnidade = dr.GetInt32(2);
            if (!dr.IsDBNull(3))
                ag.Data = dr.GetDateTime(3);
            if (!dr.IsDBNull(4))
                ag.HoraInicio = dr.GetInt32(4);
            if (!dr.IsDBNull(5))
                ag.CalendarId = dr.GetString(5);
            return ag;
        }

        private string montarSelectGoogle(AgendaView ag)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select  ID, ID_PRESTADOR, ID_UNIDADE, DATA, HORAINICIO, CALENDARID ");
            sb.Append("from GOOGLECALENDAR ");
            sb.Append("where DATA = '{0}' ");
            sb.Append("and HORAINICIO = {1} ");
            sb.Append("and ID_PRESTADOR = {2} ");
            sb.Append("and ID_UNIDADE = {3}");
            return string.Format(sb.ToString(),ag.Data.ToString("yyyy-MM-dd 00:00:00"),ag.HoraInicio,ag.Id_Prestador,ag.Id_Unidade);
        }
    }
}
